package com.zglc.ms.shop.dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.zglc.ms.shop.model.ShopHomeTemplate;
import com.zglc.ms.shop.model.ShopHomeTemplateDetail;
import com.zglc.ms.shop.model.ShopHomeTemplateDetailVo;
import com.zglc.ms.shop.model.ShopProduct;

@Repository
public class HomeTemplateDetailDao {

	private Logger logger = LoggerFactory.getLogger(getClass());

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	public int insertShopHomeTemplateDetail(ShopHomeTemplateDetail shopHomeTemplateDetail) {
		final String sql = "INSERT INTO shop_home_template_detail( "
				+ " home_template_code, product_id, template_product_type, group_id, shop_product_type, activity_link, "
				+ " activity_img_url, weight, remark, status, created_at, updated_at)"
				+ " VALUES (?, ?, ?, ?, ?, ?,   ?, ?, ?, ?, NOW(), NOW())";
		logger.debug("插入TemplateDetail： " + sql);
		logger.debug("插入参数： " + shopHomeTemplateDetail.toString());
		KeyHolder keyHolder = new GeneratedKeyHolder();
		try{	
			jdbcTemplate.update(new PreparedStatementCreator() {
	
				@Override
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
					PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
					ps.setInt(1, shopHomeTemplateDetail.getHomeTemplateId());
					ps.setInt(2, shopHomeTemplateDetail.getProductId());
					ps.setInt(3, shopHomeTemplateDetail.getTemplateProductType());
					ps.setInt(4, shopHomeTemplateDetail.getDetailGroupId());
					ps.setInt(5, shopHomeTemplateDetail.getShopProductType());
					ps.setString(6, shopHomeTemplateDetail.getActivityLink());
					
					ps.setString(7, shopHomeTemplateDetail.getActivityImgUrl());
					ps.setInt(8, shopHomeTemplateDetail.getWeight());
					ps.setString(9, shopHomeTemplateDetail.getRemark());
					ps.setInt(10, shopHomeTemplateDetail.getStatus());
					return ps;
				}
			}, keyHolder);
		}catch(Exception e){
			e.printStackTrace();
			logger.debug(e.toString());
			return -1;
		}
		return keyHolder.getKey().intValue();
	}
	
	public int UpdateShopHomeTemplateDetail(ShopHomeTemplateDetail shopHomeTemplateDetail) {
		final String sql = "REPLACE INTO shop_home_template_detail("
				+ " home_template_code, product_id, template_product_type, group_id, shop_product_type, activity_link, "
				+ " activity_img_url, weight, remark, status, created_at, updated_at, id) "
				+ " VALUES (?, ?, ?, ?, ?,   ?, ?, ?, ?, NOW(), NOW(), ?) ";
		logger.debug("更新TemplateDetail  sql： " + sql);
		logger.debug("更新参数： " + shopHomeTemplateDetail.toString());
		KeyHolder keyHolder = new GeneratedKeyHolder();
		try{	
			jdbcTemplate.update(new PreparedStatementCreator() {
	
				@Override
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
					PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
					ps.setInt(1, shopHomeTemplateDetail.getHomeTemplateId());
					ps.setInt(2, shopHomeTemplateDetail.getProductId());
					ps.setInt(3, shopHomeTemplateDetail.getTemplateProductType());
					ps.setInt(4, shopHomeTemplateDetail.getDetailGroupId());
					ps.setInt(5, shopHomeTemplateDetail.getShopProductType());
					ps.setString(6, shopHomeTemplateDetail.getActivityLink());
					
					ps.setString(7, shopHomeTemplateDetail.getActivityImgUrl());
					ps.setInt(8, shopHomeTemplateDetail.getWeight());
					ps.setString(9, shopHomeTemplateDetail.getRemark());
					ps.setInt(10, shopHomeTemplateDetail.getStatus());
					ps.setInt(11, shopHomeTemplateDetail.getId());
					return ps;
				}
			}, keyHolder);
		}catch(Exception e){
			e.printStackTrace();
			logger.debug(e.toString());
			return -1;
		}
		return keyHolder.getKey().intValue();
	}
	
	
	public ShopHomeTemplateDetail getShopHomeTemplateDetailById( String id) {
		final String sql = "SELECT * FROM shop_home_template_detail  WHERE id = ? AND status = true ORDER BY weight desc ";
		
		try {
			ShopHomeTemplateDetail info = jdbcTemplate.queryForObject(sql, new Object[]{id}, new RowMapper<ShopHomeTemplateDetail>() {

				@Override
				public ShopHomeTemplateDetail mapRow(ResultSet rs, int rowNum) throws SQLException {
					ShopHomeTemplateDetail info = setShopHomeTemplateDetail(rs);
					return info;
				}
			});
			
			return info;
		} catch (DataAccessException e) {
			logger.info("getShopProductByCode e = " + e.getLocalizedMessage());
		}
		
		return null;
	}
	
	private ShopHomeTemplateDetail setShopHomeTemplateDetail(ResultSet rs) throws SQLException{
		ShopHomeTemplateDetail info = new ShopHomeTemplateDetail();
		info.setId(rs.getInt("id"));
		info.setProductId(rs.getInt("product_id"));
		info.setHomeTemplateId(rs.getInt("home_template_id"));
		info.setTemplateProductType(rs.getInt("template_product_type"));
		info.setDetailGroupId(rs.getInt("detail_group_id"));
		info.setShopProductType(rs.getInt("shop_product_type"));
		info.setActivityLink(rs.getString("activity_link"));
		info.setActivityImgUrl(rs.getString("activity_img_url"));
		info.setWeight(rs.getInt("weight"));
		info.setRemark(rs.getString("remark"));
		info.setStatus(rs.getInt("status"));
		info.setCreatedAt(rs.getDate("created_at"));
		info.setUpdatedAt(rs.getDate("updated_at"));
		return info;
	}
	
	private ShopHomeTemplateDetailVo setShopHomeTemplateDetailVo(ResultSet rs) throws SQLException{
		ShopHomeTemplateDetailVo info = new ShopHomeTemplateDetailVo();
		info.setId(rs.getInt("id"));
		info.setProductId(rs.getInt("product_id"));
		info.setHomeTemplateId(rs.getInt("home_template_id"));
		info.setTemplateProductType(rs.getInt("template_product_type"));
		info.setDetailGroupId(rs.getInt("detail_group_id"));
		info.setShopProductType(rs.getInt("shop_product_type"));
		info.setActivityLink(rs.getString("activity_link"));
		info.setActivityImgUrl(rs.getString("activity_img_url"));
		info.setWeight(rs.getInt("weight"));
		return info;
	}
	
	public List<ShopHomeTemplateDetail> getShopHomeTemplateDetailByAll() {
		
		final String sql = "SELECT  * FROM shop_home_template_detail WHERE status = true ORDER BY weight desc ";
		try {
			List<ShopHomeTemplateDetail> users = jdbcTemplate.query(sql, new Object[] {},
					new ResultSetExtractor<List<ShopHomeTemplateDetail>>() {
						public List<ShopHomeTemplateDetail> extractData(ResultSet rs)
								throws SQLException, DataAccessException {
							List<ShopHomeTemplateDetail> list = new ArrayList<ShopHomeTemplateDetail>();
							while (rs.next()) {
								ShopHomeTemplateDetail info = setShopHomeTemplateDetail(rs);
							list.add(info);
						}
						return list;
				}
			});
			return users;
		} catch (DataAccessException e) {
			logger.error("not exist getShopHomeTemplateDetailByAll by all = "  + "|" + e.getLocalizedMessage());
			return null;
		}
	}
	
	public List<ShopHomeTemplateDetail> getShopHomeTemplateDetailByTemplateId(int templateId) {
		
		final String sql = "SELECT  * FROM shop_home_template_detail where home_template_id = ? AND status = true ORDER BY weight desc";
		try {
			List<ShopHomeTemplateDetail> users = jdbcTemplate.query(sql, new Object[] {templateId},
					new ResultSetExtractor<List<ShopHomeTemplateDetail>>() {
						public List<ShopHomeTemplateDetail> extractData(ResultSet rs)
								throws SQLException, DataAccessException {
							List<ShopHomeTemplateDetail> list = new ArrayList<ShopHomeTemplateDetail>();
							while (rs.next()) {
								ShopHomeTemplateDetail info = setShopHomeTemplateDetail(rs);
							list.add(info);
						}
						return list;
				}
			});
			return users;
		} catch (DataAccessException e) {
			logger.error("not exist getShopHomeTemplateDetailByTemplateCode by all = "  + "|" + e.getLocalizedMessage());
			return null;
		}
	}
	
	public List<ShopHomeTemplateDetail> getShopHomeTemplateDetailByTemplateIdAndGroup(int templateId) {
		
		final String sql = "SELECT * FROM ( "
				+ " SELECT  * FROM shop_home_template_detail WHERE STATUS = TRUE AND home_template_id = ? AND detail_group_id IS NULL"
				+ " UNION "
				+ " (SELECT  * FROM shop_home_template_detail WHERE "
				+ " detail_group_id IN("
				+ " SELECT a1.detail_group_id FROM ("
				+ " SELECT  detail_group_id ,COUNT(*)cnt FROM shop_home_template_detail WHERE  STATUS = TRUE AND home_template_id = ? AND detail_group_id IS NOT NULL GROUP BY detail_group_id ) a1 WHERE a1.cnt = 3 )"
				+ " AND STATUS = TRUE AND home_template_id = ? AND detail_group_id IS NOT NULL GROUP BY detail_group_id ) ) a ORDER BY a.weight DESC, a.updated_at DESC ";
		try {
			List<ShopHomeTemplateDetail> users = jdbcTemplate.query(sql, new Object[] {templateId,templateId,templateId},
					new ResultSetExtractor<List<ShopHomeTemplateDetail>>() {
						public List<ShopHomeTemplateDetail> extractData(ResultSet rs)
								throws SQLException, DataAccessException {
							List<ShopHomeTemplateDetail> list = new ArrayList<ShopHomeTemplateDetail>();
							while (rs.next()) {	
								ShopHomeTemplateDetail info = setShopHomeTemplateDetail(rs);
							list.add(info);
						}
						return list;
				}
			});
			return users;
		} catch (DataAccessException e) {
			logger.error("not exist getShopHomeTemplateDetailByTemplateCode by all = "  + "|" + e.getLocalizedMessage());
			return null;
		}
	}
	
	public List<ShopHomeTemplateDetailVo> getShopHomeTemplateDetailVoByTemplateIdAndGroup(int templateId) {
		
		final String sql = "SELECT * FROM ( "
				+ " SELECT  * FROM shop_home_template_detail WHERE STATUS = TRUE AND home_template_id = ? AND detail_group_id IS NULL"
				+ " UNION "
				+ " (SELECT  * FROM shop_home_template_detail WHERE "
				+ " detail_group_id IN("
				+ " SELECT a1.detail_group_id FROM ("
				+ " SELECT  detail_group_id ,COUNT(*)cnt FROM shop_home_template_detail WHERE  STATUS = TRUE AND home_template_id = ? AND detail_group_id IS NOT NULL GROUP BY detail_group_id ) a1 WHERE a1.cnt = 3 )"
				+ " AND STATUS = TRUE AND home_template_id = ? AND detail_group_id IS NOT NULL GROUP BY detail_group_id ) ) a ORDER BY a.weight DESC, a.updated_at DESC ";
		try {
			List<ShopHomeTemplateDetailVo> users = jdbcTemplate.query(sql, new Object[] {templateId,templateId,templateId},
					new ResultSetExtractor<List<ShopHomeTemplateDetailVo>>() {
						public List<ShopHomeTemplateDetailVo> extractData(ResultSet rs)
								throws SQLException, DataAccessException {
							List<ShopHomeTemplateDetailVo> list = new ArrayList<ShopHomeTemplateDetailVo>();
							while (rs.next()) {	
								ShopHomeTemplateDetailVo info = setShopHomeTemplateDetailVo(rs);
							list.add(info);
						}
						return list;
				}
			});
			return users;
		} catch (DataAccessException e) {
			logger.error("not exist getShopHomeTemplateDetailVoByTemplateCode by all = "  + "|" + e.getLocalizedMessage());
			return null;
		}
	}
	
	public List<ShopHomeTemplateDetail> getShopHomeTemplateDetailByTemplateIdAndGroupId(int templateId, int groupId) {

		final String sql = "SELECT shtd.* FROM shop_home_template_detail shtd INNER JOIN shop_product sp on shtd.product_id = sp.id " +
				" where shtd.home_template_id = ? And shtd.detail_group_id = ? AND shtd.status = true ORDER BY sp.weight desc, sp.updated_at desc";
		try {
			List<ShopHomeTemplateDetail> users = jdbcTemplate.query(sql, new Object[] {templateId, groupId},
					new ResultSetExtractor<List<ShopHomeTemplateDetail>>() {
						public List<ShopHomeTemplateDetail> extractData(ResultSet rs)
								throws SQLException, DataAccessException {
							List<ShopHomeTemplateDetail> list = new ArrayList<ShopHomeTemplateDetail>();
							while (rs.next()) {
								ShopHomeTemplateDetail info = setShopHomeTemplateDetail(rs);
							list.add(info);
						}
						return list;
				}
			});
			return users;
		} catch (DataAccessException e) {
			logger.error("not exist getShopHomeTemplateDetailByTemplateCode by all = "  + "|" + e.getLocalizedMessage());
			return null;
		}
	}
	
	public List<ShopHomeTemplateDetailVo> getShopHomeTemplateDetailVoByTemplateIdAndGroupId(int templateId, int groupId) {

		final String sql = "SELECT shtd.* FROM shop_home_template_detail shtd INNER JOIN shop_product sp on shtd.product_id = sp.id " +
				" where shtd.home_template_id = ? And shtd.detail_group_id = ? AND shtd.status = true ORDER BY sp.weight desc, sp.updated_at desc";
		try {
			List<ShopHomeTemplateDetailVo> users = jdbcTemplate.query(sql, new Object[] {templateId, groupId},
					new ResultSetExtractor<List<ShopHomeTemplateDetailVo>>() {
						public List<ShopHomeTemplateDetailVo> extractData(ResultSet rs)
								throws SQLException, DataAccessException {
							List<ShopHomeTemplateDetailVo> list = new ArrayList<ShopHomeTemplateDetailVo>();
							while (rs.next()) {
								ShopHomeTemplateDetailVo info = setShopHomeTemplateDetailVo(rs);
							list.add(info);
						}
						return list;
				}
			});
			return users;
		} catch (DataAccessException e) {
			logger.error("not exist getShopHomeTemplateDetailVoByTemplateCode by all = "  + "|" + e.getLocalizedMessage());
			return null;
		}
	}
	
}